import csv

from py2neo import Relationship
from py2neo import Graph, Node

rcsv = open('data/data.csv', 'r', encoding="utf-8")
reader = csv.reader(rcsv)
data = list(reader)

citycsv = open('data/city.csv', 'r', encoding="utf-8")
cityreader = csv.reader(citycsv)
cityData = list(cityreader)

citycsv = open('data/job.csv', 'r', encoding="utf-8")
cityreader = csv.reader(citycsv)
jobData = list(cityreader)

citycsv = open('data/salary.csv', 'r', encoding="utf-8")
cityreader = csv.reader(citycsv)
salaryData = list(cityreader)
graph = Graph("http://47.109.43.77/:7474", auth=("neo4j", "Xiyou666"), name="neo4j")
# 删除知识图谱中所有节点
# graph.delete_all()

#创建job节点
for i in range(1, len(data)):
    job = Node('job', id=data[i][0], company=data[i][1], name=data[i][2], salary=data[i][3], education=data[i][4],
               description=data[i][5], hiring_manager=data[i][6], last_active=data[i][7], address=data[i][8],
               link=data[i][9])
    graph.create(job)

for i in range(1, len(cityData)):
    # 通过name属性匹配或创建城市节点
    query1 = """
   MERGE (city:City {province: $province, name: $cityName})
   RETURN city
   """
    # 执行查询并传入参数
    result1 = graph.run(query1, province=cityData[i][3], cityName=cityData[i][4]).data()
    # 通过name属性匹配或创建公司节点
    query2 = """
           MERGE (company:Company {name: $companyName})
           RETURN company
           """
    # 执行查询并传入参数
    result2 = graph.run(query2, companyName=cityData[i][2]).data()

    # 创建公司与城市节点之间的关系
    query3 = """
   MATCH (city:City {province: $province, name: $cityName}), (company:Company {name: $companyName})
   MERGE (company)-[r:LOCATED_IN]->(city)
   RETURN r
   """
    # 执行查询并传入参数
    result3 = graph.run(query3, province=cityData[i][3], cityName=cityData[i][4], companyName=cityData[i][2]).data()

    # 创建公司与职位节点之间的关系
    query4 = """
    MATCH (company:Company {name: $name})
    MATCH (job:job {id: $id})
    MERGE (job)-[:BELONG_TO]->(company)
       """
    result4 = graph.run(query4, name=cityData[i][2], id=cityData[i][1])

    # 建立职位与城市之间的关系
    query5 = """
      MATCH (job:job {id: $id}),
         (city:City {province: $province, name: $cityName})
   MERGE (job)-[r:LOCATED_IN]->(city)
   RETURN r
      """
    # 执行查询并传入参数
    result5 = graph.run(query5, id=cityData[i][1], province=cityData[i][3], cityName=cityData[i][4],
                        companyName=cityData[i][2]).data()
categories = set()
demands = set()
for i in range(1, len(jobData)):
    # 获取类别名称并添加到集合中
    category_parts = jobData[i][1].split("/")[1:]
    categories.update(category_parts)

    # 获取需求名称并添加到集合中
    demand_parts = jobData[i][2].split("/")[1:]
    demands.update(demand_parts)

    # 创建类别节点
    for category in categories:
        query1 = """
           MERGE (category:skills {name: $categoryName})
           RETURN category
           """
        # 执行查询并传入参数
        result = graph.run(query1, categoryName=category).data()

    # 创建需求节点
    for demand in demands:
        query2 = """
           MERGE (demand:Demand {name: $demandName})
           RETURN demand
           """
        # 执行查询并传入参数
        result = graph.run(query2, demandName=demand).data()
for i in range(1, len(jobData)):

    categories1 = set()
    category_parts1 = jobData[i][1].split("/")[1:]
    # 创建职位与类别节点的关系
    for category in category_parts1:
        query_category = """
           MATCH (job:job {id: $id})
           MATCH (skills:skills {name: $categoryName})
           MERGE (job)-[:REQUIRES]->(skills)
           """
        # 执行查询并传入参数
        result_category = graph.run(query_category, id=jobData[i][0], categoryName=category).data()
    demands1 = set()
    demand_parts1 = jobData[i][2].split("/")[1:]
    # 创建职位与需求节点的关系
    for demand in demand_parts1:
        query_demand = """
              MATCH (job:job {id: $id})
              MATCH (demand:Demand {name: $demandName})
              MERGE (job)-[:HAS_DEMAND]->(demand)
              """
        # 执行查询并传入参数
        result_demand = graph.run(query_demand, id=jobData[i][0], demandName=demand).data()
#
for i in range(1, len(salaryData)):
    salaryData[i][2].strip()  # 删除s开头、结尾的rm
# salaryData[i][2].split(".")  # 删除s开头的rm
# 获取工资数值
    salary = float(salaryData[i][2])
    salary2 = salaryData[i][2]

# 创建工资节点
    query = """
    MERGE (salary:Salary {name: $salaryName ,salary: $salary})
    RETURN salary
    """

#执行查询并传入参数
    result = graph.run(query, salaryName=salary2, salary=salary).data()
for i in range(1, len(salaryData)):
    # 获取工资数值
    salary = salaryData[i][2]
    # 建立关系
    relation_query = """
        MATCH (job:job {id: $id})
        MATCH (salary:Salary {name: $salaryName})
        MERGE (job)-[r:HAS_SALARY]->(salary)
        RETURN r
        """

    # 执行查询并传入参数
    relation = graph.run(relation_query, id=salaryData[i][0], salaryName=salary).data()
